# -*- coding: utf-8 -*-
import PyQt4, PyQt4.QtGui, sys, csv, MySQLdb, urllib, math
from PyQt4.QtGui import QMainWindow
from PyQt4.QtCore import pyqtSignature
from PyQt4 import QtCore

from Ui_DlgMain import Ui_DlgMain

class DlgMain(QMainWindow, Ui_DlgMain):

    def __init__(self, parent = None):

        QMainWindow.__init__(self, parent)
        self.setupUi(self)
    
    @pyqtSignature("")
    def mysql_database(self, sql_str, values, code): # Database
        conn = MySQLdb.connect(host='localhost', user='root',passwd='123') #数据库连接参数
        cursor = conn.cursor()
        conn.select_db('stock')#数据库名称。都用Stock
        
        try:
            sql = "drop table `stock`.`"+str(code)+ "`;"
            cursor.execute(sql)
            sql = "create table if not exists " + str(code) + "(  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Date` date NOT NULL,  `Open` float NOT NULL,  `High` float NOT NULL,  `Low` float NOT NULL,  `Close` float NOT NULL,  `Volume` bigint(20) DEFAULT NULL,  `Adj` float NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB"
            cursor.execute(sql)
            conn.commit()
        except:
            sql = "create table if not exists " + str(code) + "(  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Date` date NOT NULL,  `Open` float NOT NULL,  `High` float NOT NULL,  `Low` float NOT NULL,  `Close` float NOT NULL,  `Volume` bigint(20) DEFAULT NULL,  `Adj` float NOT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB"
            cursor.execute(sql)
            conn.commit()
        cursor.executemany(sql_str, values)
        conn.commit()
        
        cursor.close()
        conn.close()
    
    @pyqtSignature("")
    def domainwriter(self, reader):
        values=[]
        for obj1 in reader:
            values.append(( obj1['Date'], obj1['Open'], obj1['High'], obj1['Low'], obj1['Close'], obj1['Volume'], obj1['Adj Close'], ))  #csv title
        return values
        
    @pyqtSignature("")
    def codereader(self,  nowid):
        try:
            connread = MySQLdb.connect(host='localhost',user='root',passwd='123',db='stock')#读取列表，db=Nasdaq或者stock
            cursorread = connread.cursor()
            sql = "select code from list where id = "+str(nowid)
            cursorread.execute(sql)
            codelist = cursorread.fetchall()
            code = codelist[0][0]
            cursorread.close()
            connread.close()
        except Exception, e:
            print e
            sys.exit()
        return code
        
    @pyqtSignature("")
    def connectcleandb(self, codesql, conn, cursor):
        try:
            sql = "drop table `stock`.`data"+str(codesql)+ "`;"
            cursor.execute(sql)
            sql = "CREATE TABLE `data"+ str(codesql) +"` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
            cursor.execute(sql)
            conn.commit()
        except:
            sql = "CREATE TABLE `data"+ str(codesql) +"` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` int(11) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
            cursor.execute(sql)
            conn.commit()
    def cleanearn(self):
        conn = MySQLdb.connect(host='localhost',user='root',passwd='123',db='stock')#写入股票表，db=stock
        cursor = conn.cursor()
        try:
            sql = "drop table `stock`.`earnorder`;"
            cursor.execute(sql)
            sql = "CREATE TABLE `earnorder` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` varchar(45) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
            cursor.execute(sql)
            conn.commit()
        except:
            sql = "CREATE TABLE `earnorder` (  `identifier` int(11) NOT NULL AUTO_INCREMENT,  `id` varchar(45) NOT NULL,  `value` double NOT NULL,  PRIMARY KEY (`identifier`)) ENGINE=InnoDB"
            cursor.execute(sql)
            conn.commit()
 #---------------------------------------------------------------------------------------------------------------------
    @pyqtSignature("")
    def on_btnGetData_clicked(self):
        Network=1
        for nowid in range(1, 50):  #48 hk stock, 71 funds
            print "Start data request "+ str(nowid)
            code=self.codereader(nowid)
            if (Network==1):
                try:
                    FILE_PATH = 'presentstockdata.csv' #路径
                    urllib.urlretrieve("http://ichart.finance.yahoo.com/table.csv?s="+ str(code) +"&a=00&b=2&c=2011&d=06&e=2&f=2012&g=d&ignore=.csv", FILE_PATH)
                except Exception, e:
                    print sys.exc_info()[0],sys.exc_info()[1]
                    sys.exit()
                print "Start SQL write request of " + str(code)
                
                if cmp(".hk",code[4:6])==1:
                    codesql = "hk" + filter(str.isdigit, code)
                else:
                    codesql =code
                
                INSERT_SQL = {'domain': 'insert into ' + str(codesql) + ' values(NULL, %s, %s, %s, %s, %s, %s, %s )'}  #有个自增的ID列用Null区分开，test2为表名，平时为股票代号
                INSERT_VALUES = {'domain': self.domainwriter}
            
                try:
                    fd = open(FILE_PATH)            # get file connection
                    dialect = csv.Sniffer().sniff(fd.readline())
                    fd.seek(0)
                    reader = csv.DictReader(fd, dialect = dialect)
                    self.mysql_database(INSERT_SQL['domain'], INSERT_VALUES['domain'](reader), codesql) # insert into MySQL database
                    print "Complete SQL write request of " + str(code)
                except:
                    print sys.exc_info()[0],sys.exc_info()[1]
                finally:
                    fd.close()
            #Network Detect End
 #---------------------------------------------------------------------------------------------------------------------
    @pyqtSignature("")
    def on_btnAnalyse_clicked(self):
        for nowid in range(1, 50):  
            code=self.codereader(nowid)
            print "Start Analyse request of " + str(code)
            try:
                connread =  MySQLdb.connect(host='localhost',user='root',passwd='123',db='stock')#读取列表，db=Nasdaq或者stock
                connwrite = MySQLdb.connect(host='localhost',user='root',passwd='123',db='stock')#写入股票表，db=stock
            except Exception, e:
                print e
                sys.exit()
            
            if cmp(".hk",code[4:6])==1:
                codesql = "hk" + filter(str.isdigit, code)
            else:
                codesql =code
                
            cursorread = connread.cursor()
            cursorwrite = connwrite.cursor()
            sqlread = "select adj from "+ str(codesql) + " where id % 8 = 1"
            cursorread.execute(sqlread)
            pricelist = cursorread.fetchall()
            cursorread.close()
            connread.close()
            self.connectcleandb(codesql, connwrite,  cursorwrite)
            unum=[]
            
            for lpid in range(0, 30):
                unum.append(math.log(pricelist[lpid+1][0]/pricelist[lpid][0],math.e))
            s1=0
            s2=0
            for count in unum:
                s1 += count
                s2 += count ** 2
            
            s = math.sqrt(1.0/30.0*s2 - 1.0/870.0*s1**2)
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (100, s)
            cursorwrite.execute(sql)
            connwrite.commit()
            #T=0.0323886, sqrtT=0.179968330547, n=30, 每8个元素一个周期，一年31个采样点，每个相隔12天
            Timelen=0.0323886
            miu=0.169
            sigma = s/math.sqrt(Timelen)
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (101, sigma)
            cursorwrite.execute(sql)
            connwrite.commit()
            
            temp1=math.sqrt(s/Timelen)
            lower = miu - temp1-(sigma**2)/2.0
            upper = miu+ temp1-(sigma**2)/2.0
            pricehigh=pricelist[0][0] * math.e ** (upper*Timelen)
            pricelow= pricelist[0][0] * math.e ** (lower*Timelen)
            
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (102, pricelow)
            cursorwrite.execute(sql)
            connwrite.commit()
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (103, pricehigh)
            cursorwrite.execute(sql)
            connwrite.commit()
            earning= ( pricehigh - pricelist[0][0] )/ pricelist[0][0]
            sql = "insert into data"+ str(codesql) + "(identifier, id, value) values (null,'%s', %s)" % (104, earning)
            cursorwrite.execute(sql)
            connwrite.commit()
            cursorwrite.close()
            connwrite.close()
        #End of loop
        self.cleanearn()
        for nowid in range(1, 50):  
            try:
                connread =  MySQLdb.connect(host='localhost',user='root',passwd='123',db='stock')#读取列表，db=Nasdaq或者stock
                connwrite = MySQLdb.connect(host='localhost',user='root',passwd='123',db='stock')#写入股票表，db=stock
            except Exception, e:
                print e
                sys.exit()
            
            if cmp(".hk",code[4:6])==1:
                codesql = "hk" + filter(str.isdigit, code)
            else:
                codesql =code

            try:
                code=self.codereader(nowid)
                if cmp(".hk",code[4:6])==1:
                    codesql = "hk" + filter(str.isdigit, code)
                else:
                    codesql =code
                cursorread = connread.cursor()
                cursorwrite = connwrite.cursor()
                sqlread = "select value from data"+ str(codesql) + " where id = 104"
                cursorread.execute(sqlread)
                earn = cursorread.fetchall()
                sql = "insert into earnorder (identifier, id, value) values (null,'%s', %s)" % (code, earn[0][0])
                cursorwrite.execute(sql)
                connwrite.commit()

                cursorwrite.close()
                connwrite.close()
            except:
                print sys.exc_info()[0],sys.exc_info()[1]
        sqlread = "select id, value from earnorder order  by  value desc"
        cursorread.execute(sqlread)
        topearn = cursorread.fetchall()

        amount=50000.0
        print("Best pick:")
        for n in range(5):
            print (str(topearn[n][0])+"  "+str(topearn[n][1]*100)[0:5]+"%")
        sum=float(topearn[0][1])+float(topearn[1][1])
        buy1=int(float(topearn[0][1])/float(sum)*amount)
        buy2=int(amount-buy1)
        sqlread = 'select name from list where code = "'+topearn[0][0] +'"'
        cursorread.execute(sqlread)
        topname1 = cursorread.fetchall()
        sqlread = 'select name from list where code = "'+topearn[1][0] +'"'
        cursorread.execute(sqlread)
        topname2 = cursorread.fetchall()
        print("Buy "+ str(topearn[0][0])+" "+str(topname1[0][0])+" for "+ str(buy1) + ", and "+str(topearn[1][0]) +" "+str(topname2[0][0])+" for " +str(buy2))
        print("Earning："+str(buy1*float(topearn[0][1])+buy2*float(topearn[1][1]) )[0:7])
        print "Predicted Price is saved in MySQL and recommendation has decided."
        cursorread.close()
        connread.close()
if __name__ == "__main__":
    app = PyQt4.QtGui.QApplication(sys.argv)
    myapp = DlgMain()
    myapp.show()
    sys.exit(app.exec_())
